-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE [WarehouseManagement]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		TuyenVD
-- Create date: 3/16/2011
-- Description:	This stored procedure use to insert an Employee record
-- =============================================
ALTER PROCEDURE [InsertEmployee]
(
	@FullName NVARCHAR(50)
	, @DateOfBirth DATE
	, @IdentityCardNumber nchar(9)
	, @Gender NVARCHAR(10)
	, @JoinedDate DATE
	, @Email VARCHAR(50) = NULL
	, @Address NVARCHAR(MAX)
	, @PhoneNumber VARCHAR(MAX) = NULL
	, @MobileNumber VARCHAR(MAX) = NULL
	, @Description NVARCHAR(MAX) = NULL
	, @EmploymentTypeID INT
	, @RoleID INT
	, @Username NVARCHAR(MAX)
	, @EmployeeID INT = -1 OUTPUT
	, @AuthorizationIDList NVARCHAR(MAX) = NULL
)
AS
BEGIN
	
	IF (NOT EXISTS (SELECT 1 FROM Employee WHERE [Username] = @Username))
	BEGIN
		INSERT INTO [Employee] 
		(
				[FullName] 
				, [DateOfBirth] 
				, [IdentityCardNumber] 
				, [Email] 
				, [Gender] 
				, [JoinedDate] 
				, [Address] 
				, [PhoneNumber] 
				, [MobileNumber]
				, [Description] 
				, [EmploymentTypeID] 
				, [RoleID] 
				, [Username]
				, [Password]
		) 
		VALUES
		(
				@FullName
				, @DateOfBirth
				, @IdentityCardNumber
				, @Email
				, @Gender
				, @JoinedDate
				, @Address
				, @PhoneNumber
				, @MobileNumber
				, @Description
				, @EmploymentTypeID
				, @RoleID
				, @Username
				, @Username + '@12345'
		)

		SELECT @EmployeeID = IDENT_CURRENT('Employee')
		--Set EmployeeID to -1 when inserting unsuccessfully.
		IF (EXISTS (SELECT 1 FROM [Employee] WHERE [EmployeeID] = @EmployeeID AND [UserName] = @Username))
		BEGIN
			IF (@AuthorizationIDList IS NOT NULL)
			BEGIN
				------------------------------
				DECLARE @XmlPointer INT
	 
				EXEC sp_xml_preparedocument @XmlPointer OUTPUT, @AuthorizationIDList

				INSERT INTO 
						AccountAuthorization(EmployeeID, AuthorizationID)
				SELECT 
						@EmployeeID
						, Data.ID
				FROM OpenXml(@XmlPointer, 'Data/Item', 2)
				WITH (ID INT) AS Data
			 
				INNER JOIN --check of the valid ID from the list
						[Authorization] a
				ON
						a.[AuthorizationID] = Data.[ID]
					
				EXEC sp_xml_removedocument @XmlPointer	
				------------------------------
		END
		ELSE
			SELECT @EmployeeID = -1

	END
	
END

/*
USE WarehouseManagement
GO

DECLARE @EmployeeID INT = -1

EXEC [InsertEmployee] @FullName = 'Partner Administrator', 
	@DateOfBirth ='1989-01-01',
	@IdentityCardNumber = '123456789',
	@Gender = 'Nam',
	@JoinedDate = '2011-01-03',
	@Email = 'TuyenVD@gmail.com',
	@Address = 'Hanoi',
	@PhoneNumber = 'XXXXXXXXXX',
	@Description = 'sadvadfbhtkrtu', 
	@EmploymentTypeID = 1,
	@RoleID = 1,
	@Username='padmin',
	@AuthorizationIDList = '', 
	@EmployeeID = @EmployeeID OUT

	SELECT * FROM Employee WHERE EmployeeID = @EmployeeID
	SELECT * FROM AccountAuthorization WHERE EmployeeID = @EmployeeID
*/